package com.navi.loaddata.loadService.impl.shtWipLoad;

import com.navi.loaddata.core.entity.shtwip.ProductWipHis;
import com.navi.loaddata.core.etlException.ETLException;
import com.navi.loaddata.core.gpEntity.*;
import com.navi.loaddata.core.mysqlEntity.EtlConfD;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.core.repository.impl.MesRepositoryImpl;
import com.navi.loaddata.core.util.JdbcUtil2;
import com.navi.loaddata.core.util.LoggerUtil;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.lang.reflect.InvocationTargetException;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

import static java.util.stream.Collectors.groupingBy;

/**
 * @author chen
 */
@Service
@Transactional(transactionManager = "secondaryPlatformTransactionManager",
        rollbackFor = {RuntimeException.class, Exception.class})
public class LoadShtWipExtService {
    private static final Logger logger = LoggerFactory.getLogger(LoadShtWipExtService.class);
    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");

    @Autowired
    private MesRepositoryImpl mesDao;

    @Autowired
    private GreenPlumRepositoryImpl gpDao;

    private final static Map<String, String> dataMap = new HashMap<String, String>();

    static {
        dataMap.put("FACTORYNAME", "factory_name");
        dataMap.put("SUBFACTORYNAME", "subfactory_name");
        dataMap.put("OLDPROCESSOPERATIONNAME", "ope_id");
        dataMap.put("MACHINENAME", "eqpt_id");
        dataMap.put("MACHINERECIPENAME", "recipe_id");
        dataMap.put("PRODUCTNAME", "productName");
        dataMap.put("ARRAYGLASSNAME", "tft_glass_id");
        dataMap.put("CFGLASSNAME", "cf_glass_id");
        dataMap.put("LOTNAME", "lotName");
        dataMap.put("GROUPNAME", "groupName");
        dataMap.put("PRODUCTSPECNAME", "productSpecName");
        dataMap.put("ERPPRODUCTSPECNAME", "erpProductSpecName");
        dataMap.put("PRODUCTREQUESTNAME", "productRequestName");
        dataMap.put("ERPPRODUCTREQUESTNAME", "erpProductRequestName");
        dataMap.put("PRODUCTTHICKNESS", "productThickness");
        dataMap.put("MASKNAME", "maskName");
        dataMap.put("NGMARK", "ngMark");
        dataMap.put("PRODUCTJUDGE", "productJudge");
        dataMap.put("PRODUCTGRADE", "productGrade");
        dataMap.put("PANELGRADES", "panelGrades");
        dataMap.put("DEFECTCODES", "defectCodes");
        dataMap.put("TFTPOLLOTNAME", "tft_pol_lot_name");
        dataMap.put("TFTPOLPARTNO", "tft_pol_part_no");
        dataMap.put("CFPOLLOTNAME", "cf_pol_lot_name");
        dataMap.put("CFPOLPARTNO", "cf_pol_part_no");
        dataMap.put("OWNERID", "ownerId");
        dataMap.put("PRODUCTIONTYPE", "productionType");
        dataMap.put("OWNERTYPE", "ownerType");
        dataMap.put("EVENTNAME", "eventName");
        dataMap.put("EVENTTIME", "event_timestamp");
        dataMap.put("PANELOXS", "panel_oxs");
    }

    public int loadDataToTmp(EtlConfD etlConfD) throws ETLException {
        Session session = mesDao.getEntityManagerSession();
        int count = 0;
        try {
            logger.info("Job {} start running.{}---{}---{}", "LoadShtWipExtService", etlConfD.getRunStartTimestamp(), etlConfD.getRunEndTimestamp(), etlConfD.getExt1());
            count = loadData(session, etlConfD);
            logger.info(" LoadShtWipExtService finish");
            return count;
        } catch (ETLException e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new ETLException(e.getMessage());
        }
    }

    public int loadDataToExt(EtlConfD etlConfD) throws ETLException {
        Session session = gpDao.getEntityManagerSession();
        int count = 0;
        try {
            logger.info("Job {} start running.{}-{}", "LoadShtWipExtService", etlConfD.getRunStartTimestamp(), etlConfD.getRunEndTimestamp());
            empotyTmp(session, "stage.wpp_sht_ope_info_ext_merge");
            empotyTmp(session, "stage.wpp_sht_ope_info_ext_distinct");
            String tableName = "wpp_sht_ope_info_ext";
            StringBuffer sqlbuffColumn = new StringBuffer();
            sqlbuffColumn.append(" SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + tableName + "' " +
                    " ORDER BY ORDINAL_POSITION;");
            List<String> tableColumns = gpDao.findBySQL(sqlbuffColumn.toString());  //获取表中所有列名
            tmpToDistinct(session, tableColumns, "wpp_sht_ope_info_ext_tmp", "wpp_sht_ope_info_ext_distinct");
            distinctToMerge(session, tableColumns, "wpp_sht_ope_info_ext_distinct", "wpp_sht_ope_info_ext_merge", "wpp_sht_ope_info_ext");
            mergeToInfo(session, tableColumns, "wpp_sht_ope_info_ext_merge", "wpp_sht_ope_info_ext");

            List<String> distinct = gpDao.findBySQL(" SELECT d.panel_id FROM stage.wpp_sht_ope_info_ext_distinct d where 1=1");
            List<String> merges = gpDao.findBySQL(" SELECT m.panel_id FROM stage.wpp_sht_ope_info_ext_merge m where 1=1");
            updateWppShtOpeInfoExtData(session, "ARRAY", "wpp_asht_ope_his");
            updateWppShtOpeInfoExtData(session, "CF", "wpp_fsht_ope_his");
            empotyTmp(session, "stage.wpp_sht_ope_info_ext_merge");
            empotyTmp(session, "stage.wpp_sht_ope_info_ext_distinct");
//            logger.info("delete over :merges.size()=" + merges.size() + "---distinct.size()=" + distinct.size());
            if (merges.size() > 0 && distinct.size() > 0) {
                empotyTmp(session, "stage.wpp_sht_ope_info_ext_tmp");
                logger.info(" 清空 distinct，merge，tmp");
            } else {
                logger.info(" 只清空 distinct，merge，不清空OpeInfoexttmp");
            }

            //   updateWppShtOpeInfoExtData(session,"OC","wpp_csht_ope_his"); 多片panel补全一张大板，取值不明确；
            return distinct.size();
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            if ("ERROR".equals(e.getMessage().substring(0, 5))) {
                throw e;
            }
            throw new ETLException("ERROR loadDatOpeTmpToOpeInfo INSERT:" + e.getMessage());
        }
    }


    /**
     * Load MES HIS
     *
     * @param session
     * @param etlConfD
     */
    private int loadData(Session session, EtlConfD etlConfD) throws ETLException {
        int countSize = 0;

        QueryRunner qr_mes = JdbcUtil2.getMesProdRunner();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String etlConfDstartTime = sdf.format(etlConfD.getRunStartTimestamp().getTime());
        String etlConfDendTime = sdf.format(etlConfD.getRunEndTimestamp().getTime());
        String sql = "SELECT" +
                " H.FACTORYNAME AS factory_name," +
                " H.SUBFACTORYNAME AS subfactory_name," +
                " H.OLDPROCESSOPERATIONNAME AS OPE_ID," +
                " H.MACHINENAME AS EQPT_ID," +
                " H.MACHINERECIPENAME AS RECIPE_ID," +
                " H.PRODUCTNAME AS productName," +
                " H.ARRAYGLASSNAME AS TFT_GLASS_ID ," +
                " H.CFGLASSNAME AS CF_GLASS_ID," +
                " H.LOTNAME AS lotName," +
                " H.GROUPNAME AS groupName ," +
                " H.PRODUCTSPECNAME AS productSpecName," +
                " S.ERPPRODUCTSPECNAME AS erpProductSpecName," +       //table
                " H.PRODUCTREQUESTNAME AS productRequestName," +
                " R.ERPPRODUCTREQUESTNAME AS erpProductRequestName," +   //table
                "S.PRODUCTTHICKNESS AS productThickness," +     //table
                " H.MASKNAME AS maskName," +
                " H.NGMARK AS ngMark," +
                " H.PRODUCTJUDGE AS productJudge," +
                " H.PRODUCTGRADE AS productGrade," +
                " H.PANELGRADES AS panelGrades," +
                " H.DEFECTCODES AS defectCodes," +
                " H.TFTPOLLOTNAME tft_pol_lot_name ," +
                " H.TFTPOLPARTNO AS tft_pol_part_no," +
                " H.CFPOLLOTNAME AS cf_pol_lot_name," +
                " H.CFPOLPARTNO AS cf_pol_part_no," +
                " H.OWNERID AS ownerId," +
                " H.PRODUCTIONTYPE AS productionType," +
                " H.OWNERTYPE AS ownerType," +
                " H.EVENTNAME AS eventName," +
                " H.EVENTTIME AS  EVENT_TIMESTAMP," +
                " H.PANELOXS AS  panel_oxs" +
                " FROM " +
                "  MES_PRODUCTHISTORY H" +
                " LEFT JOIN MES_PRODUCTREQUEST R ON H.PRODUCTREQUESTNAME = R.PRODUCTREQUESTNAME" +    //ERPPRODUCTREQUESTNAME
                " LEFT JOIN MES_PRODUCTSPEC S ON H.PRODUCTSPECNAME = S.PRODUCTSPECNAME" +

                " WHERE " +
                " H.EVENTNAME IN('TrackOut','TrackIn','Ship')" +
                //     "  AND  H.PRODUCTNAME like 'C17BQ005%'   " +
                //  "  and H.OLDPROCESSOPERATIONNAME = 'C7800' " +

                //      "  AND  H.TIMEKEY >= '20171125235931'   AND H.TIMEKEY <  '20171126000409' " +
                //  " AND  H.TIMEKEY >= '" + sdf.format(etlConfD.getRunStartTimestamp()) + "' " +
                // "  AND H.TIMEKEY <  '" + sdf.format(etlConfD.getRunEndTimestamp()) + "' " +
                " AND  H.UPDATETIME >= to_date('" + etlConfDstartTime + "','yyyymmddhh24miss') " +
                "  AND H.UPDATETIME <  to_date('" + etlConfDendTime + "','yyyymmddhh24miss')" +
                //  "  AND H.PANELOXS IS NOT NULL  "+
                " ORDER BY H.UPDATETIME";
        List<ProductWipHis> productHisList = new ArrayList<>();
        try {
            long startTime = System.currentTimeMillis();
            productHisList = qr_mes.query(sql, new BeanListHandler<ProductWipHis>(ProductWipHis.class));
            long endTime1 = System.currentTimeMillis();
            long minute1 = (endTime1 - startTime) / 60 / 1000;
            logger.info("Job {} mes_sql 执行了：{} 分钟 ", "LoadShtWipExtService", minute1);
            if (productHisList == null || productHisList.size() <= 0) return 0;
            //启动加载ope_conf_ext
            List<WppShtOpeConfExt> opeConfExtList = gpDao.list(WppShtOpeConfExt.class, "from WppShtOpeConfExt where valigFlg = 'Y'");
            Map<String, List<WppShtOpeConfExt>> opeConfExtMap = new HashMap<>();
            //  List<WppShtOpeConfExt>  opeCExtList = new ArrayList<>();
            /*opeConfExtList.forEach(ope -> {
                String opeId = ope.getOpeId();
                String targetColumn = ope.getTargetColumn();
                String eventName = ope.getEventName();
                String sourceColumn = ope.getSourceColumn();
                opeConfExtMap.put(opeId + "@" + targetColumn, eventName + "@" + sourceColumn);
            });*/
            opeConfExtMap = opeConfExtList.stream().collect(groupingBy(o -> o.getOpeId() + o.getEventName()));
            long startTimes = System.currentTimeMillis();
            saveShtInfoExt(productHisList, opeConfExtMap);  //存入扩展表  WPP_SHT_OPE_INFO_EXT
            long endTimes = System.currentTimeMillis();
            long minutes = (endTimes - startTimes) / 60 / 1000;
            logger.info("Job {} saveShtInfoExt(productHisList, opeConfExtMap) 执行了：{} 分钟 ,count:{}", "LoadShtWipExtService", minutes, productHisList.size());
        } catch (Exception e) {
            logger.error("从MES查询数据：sql----------    " + sql);
            logger.error("Job {} error message: {}", "LoadShtWipExtService", LoggerUtil.stackTraceToString(e));
            throw new ETLException("Job LoadShtWipExtService error message:" + e.getMessage());
        }

        return productHisList.size();
    }

    private void saveShtInfoExt(List<ProductWipHis> productHisList, Map<String, List<WppShtOpeConfExt>> opeConfExtMap) throws ETLException {
        Map<String, List<ProductWipHis>> productHisTree =
                productHisList.stream().collect(groupingBy(ProductWipHis::getProductName));
        try {
            for (Map.Entry<String, List<ProductWipHis>> entry : productHisTree.entrySet()){
 //               productHisTree.forEach((panelId, productHisListByPanel) -> {
                    WppShtOpeInfoExtTmp wppShtOpeInfoExtTmp = new WppShtOpeInfoExtTmp();
                wppShtOpeInfoExtTmp.setPanelId(entry.getKey());
         //           wppShtOpeInfoExtTmp.setPanelId(panelId);
        //            for (ProductWipHis productHis : productHisListByPanel) {
                for (ProductWipHis productHis : entry.getValue()) {
                        String opeId = productHis.getOpe_id();
                        String eventName = productHis.getEventName();
                        List<WppShtOpeConfExt> opeCExtList = new ArrayList<>();
                        opeCExtList = opeConfExtMap.get(opeId + eventName);
                        if (null == opeCExtList || opeCExtList.size() <= 0) continue;
                        for (WppShtOpeConfExt opeCExt : opeCExtList) {
                            String tarSeq = opeCExt.getTargetColumn();
                            String sourceColumn = opeCExt.getSourceColumn();
                            sourceColumn = dataMap.get(sourceColumn);
                            //                  try{
                            String sourceValue = BeanUtils.getProperty(productHis, sourceColumn);
                            BeanUtils.setProperty(wppShtOpeInfoExtTmp, tarSeq, sourceValue);
                      /*  }catch (IllegalAccessException e) {
                            logger.error("Job LoadShtWipExtService error message :saveShtInfoExt:  " + LoggerUtil.stackTraceToString(e));
                        } catch (InvocationTargetException e) {
                            logger.error("Job LoadShtWipExtService error message :saveShtInfoExt:  " + LoggerUtil.stackTraceToString(e));
                        } catch (NoSuchMethodException e) {
                            logger.error("Job LoadShtWipExtService error message :saveShtInfoExt:  " + LoggerUtil.stackTraceToString(e));
                        }*/

                        }
                    }
                    gpDao.save(wppShtOpeInfoExtTmp);
                    //gpDao.update(wppShtOpeInfoExt);
                    //         });
                }
        } catch (Exception e) {
            throw new ETLException("Job LoadShtWipExtService error message:" + e.getMessage());
            //       logger.error("LoadShtWipExtService  gpDao.save(wppShtOpeInfoExtTmp) error" +e.getMessage() +"wppShtOpeInfoExtTmp:"+wppShtOpeInfoExtTmp.toString());
        }
        /*productHisTree.forEach((panelId, productHisListByPanel) -> {
            //  List<ProductWipHis> productWipHisListByPanel = productHisTree.get(panelId);


            WppShtOpeInfoExtTmp wppShtOpeInfoExtTmp = new WppShtOpeInfoExtTmp();
            wppShtOpeInfoExtTmp.setPanelId(panelId);
            for (ProductWipHis productHis : productHisListByPanel) {
                String opeId = productHis.getOpe_id();
                String eventName = productHis.getEventName();
                List<WppShtOpeConfExt>  opeCExtList = new ArrayList<>();
                opeCExtList = opeConfExtMap.get(opeId+eventName);
                if (null ==opeCExtList || opeCExtList.size() <= 0) continue;

                for (WppShtOpeConfExt opeCExt : opeCExtList) {

                    String tarSeq = opeCExt.getTargetColumn();
                    String sourceColumn = opeCExt.getSourceColumn();
                    sourceColumn = dataMap.get(sourceColumn);
                    try {
                        String sourceValue = BeanUtils.getProperty(productHis, sourceColumn);
                        BeanUtils.setProperty(wppShtOpeInfoExtTmp, tarSeq, sourceValue);
                    } catch (IllegalAccessException e) {
                        logger.error("Job LoadShtWipExtService error message :saveShtInfoExt:  " + LoggerUtil.stackTraceToString(e));
                    } catch (InvocationTargetException e) {
                        logger.error("Job LoadShtWipExtService error message :saveShtInfoExt:  " + LoggerUtil.stackTraceToString(e));
                    } catch (NoSuchMethodException e) {
                        logger.error("Job LoadShtWipExtService error message :saveShtInfoExt:  " + LoggerUtil.stackTraceToString(e));
                    }

                }
            }
            try{
                gpDao.save(wppShtOpeInfoExtTmp);
            }catch(Exception e){
                logger.error("LoadShtWipExtService  gpDao.save(wppShtOpeInfoExtTmp) error" +e.getMessage() +"wppShtOpeInfoExtTmp:"+wppShtOpeInfoExtTmp.toString());
            }

            //gpDao.update(wppShtOpeInfoExt);
        });*/
    }


    private void tmpToDistinct(Session session, List<String> tableColumns, String tmpTableName, String distinctTableName) throws ETLException {
        //tmpTableName ="wpp_sht_info_tmp";
        //distinctTableName="wpp_sht_info_distinct";
        StringBuffer sqlbuffDistinct = new StringBuffer();
        try {
            //存储中间结果

            sqlbuffDistinct.append(" INSERT INTO STAGE." + distinctTableName + " (panel_id  ");
            tableColumns.forEach(column -> {
                if (column.equals("panel_id")) return;
                sqlbuffDistinct.append("," + column);
            });
            sqlbuffDistinct.append(" ) ");

            sqlbuffDistinct.append(" SELECT DISTINCT ON (PANEL_ID) ");
            sqlbuffDistinct.append("  PANEL_ID");
            //对每一字段做更新处理，若为空，保留原值
            tableColumns.forEach(column -> {
                if (column.equals("panel_id")) return;
                sqlbuffDistinct.append(", first_value(" + column + " ) over (partition by panel_id order by ");
                sqlbuffDistinct.append(" (CASE WHEN " + column + " IS NULL THEN NULL ELSE db_timestamp END),");
                sqlbuffDistinct.append(column + " is not null DESC ) AS " + column);
            });
            sqlbuffDistinct.append("  FROM stage." + tmpTableName + "  where 1=1");

            SQLQuery queryLoadStep = session.createSQLQuery(sqlbuffDistinct.toString());
            int count = queryLoadStep.executeUpdate();
            logger.info(tmpTableName + "  To  " + distinctTableName + "  count :" + count);
        } catch (Exception e) {
            logger.error("************   sqlbuffDistinct.toString():  **********    " + sqlbuffDistinct.toString());
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new ETLException("ERROR tmpToDistinct INSERT:" + e.getMessage());
        }

    }

    private void distinctToMerge(Session session, List<String> tableColumns, String distinctTableName, String mergeTableNmae, String finalTableName) throws ETLException {
        //distinctTableName="wpp_sht_info_distinct";
        //mergeTableNmae="wpp_sht_info_merge";
        //finalTableName="wpp_sht_info";
        //合并写入
        StringBuffer sqlbuffMerge = new StringBuffer();
        try {

            sqlbuffMerge.append(" INSERT INTO STAGE." + mergeTableNmae + " (panel_id  ");

            tableColumns.forEach(column -> {
                if (column.equals("panel_id")) return;
                sqlbuffMerge.append("," + column);
            });
            sqlbuffMerge.append(" ) ");
            sqlbuffMerge.append(" SELECT D.PANEL_ID ");
            //对每一字段做更新处理
            tableColumns.forEach(column -> {
                if (column.equals("panel_id")) return;
                sqlbuffMerge.append(", coalesce(D." + column + ", I." + column + ") " + column);
            });
            sqlbuffMerge.append("  FROM STAGE." + distinctTableName + " AS  D  LEFT JOIN  " + finalTableName + "  I  USING (PANEL_ID)");  //left join ? inner join

            SQLQuery queryLoadStep = session.createSQLQuery(sqlbuffMerge.toString());
            int count = queryLoadStep.executeUpdate();
            logger.info(distinctTableName + "  To  " + mergeTableNmae + "  count :" + count);
        } catch (Exception e) {
            logger.error("************   sqlbuffMerge.toString():  **********    " + sqlbuffMerge.toString());
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new ETLException("ERROR tmpToDistinct INSERT:" + e.getMessage());
        }
    }

    private void mergeToInfo(Session session, List<String> tableColumns, String mergeTableName, String finalTableName) throws ETLException {
        // mergeTableName ="WPP_SHT_INFO";
        //finalTableName ="WPP_SHT_INFO_MERGE";
        String sqlDelete = " DELETE FROM " + finalTableName + "  I USING STAGE." + mergeTableName + " AS M  WHERE I.PANEL_ID=M.PANEL_ID ";
        StringBuffer sqlbuffInfo = new StringBuffer();
        try {
            // DELETE USING，删除全量表的符合条件的记录。
            SQLQuery queryLoadStepD = session.createSQLQuery(sqlDelete);
            queryLoadStepD.executeUpdate();
            //  INSERT wpp_sht_info

            sqlbuffInfo.append(" INSERT INTO " + finalTableName + " (panel_id");
            tableColumns.forEach(column -> {
                if (column.equals("panel_id")) return;
                sqlbuffInfo.append("," + column);
            });
            sqlbuffInfo.append(" ) ");
            //  sqlbuffInfo.append(" SELECT D.* FROM WPP_SHT_INFO_DISTINCT D LEFT JOIN STAGE.WPP_SHT_INFO_MERGE M USING (PANEL_ID) WHERE M.* IS NULL   " );
            //  sqlbuffInfo.append(" UNION ALL   "   );
            sqlbuffInfo.append("  SELECT panel_id");
            tableColumns.forEach(column -> {
                if (column.equals("panel_id")) return;
                sqlbuffInfo.append("," + column);
            });
            sqlbuffInfo.append("  FROM STAGE." + mergeTableName);
            SQLQuery queryLoadStep = session.createSQLQuery(sqlbuffInfo.toString());
            int count = queryLoadStep.executeUpdate();
            logger.info(mergeTableName + "  To  " + finalTableName + " count :" + count);
        } catch (Exception e) {
            logger.error("************   sqlDelete.toString():  **********    " + sqlDelete.toString());
            logger.error("************   sqlbuffInfo.toString():  **********    " + sqlbuffInfo.toString());
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new ETLException("ERROR mergeToInfo :" + e.getMessage());
        }


    }

    //清空临时表
    private void empotyTmp(Session session, String tableName) throws ETLException {
        StringBuffer sqlbuff = new StringBuffer();
        try {

            sqlbuff.append(" TRUNCATE TABLE " + tableName);
            SQLQuery queryLoadStep = session.createSQLQuery(sqlbuff.toString());
            int count = queryLoadStep.executeUpdate();
            logger.info("TRUNCATE table :" + tableName + "   count :" + count);
        } catch (Exception e) {
            logger.error("************   empotyTmp.toString():  **********    " + sqlbuff.toString());
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new ETLException("ERROR TRUNCATE TABLE " + tableName + " :" + e.getMessage());
        }
    }


    /**
     * 对WppShtOpeInfoExt 表进行大板，小板 array,cf,oc阶段数据互补
     */
    //用ARRAY补全OC，CF数据

    //用CF补全ARRAY，OC数据
    //用OC补全ARRAY，CF数据
    private void updateWppShtOpeInfoExtData(Session session, String shopId, String tableName) throws ETLException {
        StringBuffer sqlbuff = new StringBuffer();
        StringBuffer sqlColumn = new StringBuffer();
        WppShtOpeInfoExtTmp wppShtOpeInfoExtTmp = new WppShtOpeInfoExtTmp();
        sqlColumn.append("select target_column from wpp_sht_ope_conf_ext where shop_id='" + shopId + "' and  valig_flg = 'Y';");
        try {
            //获取所需补全的栏位
            //   String  shopId= "ARRAY";
            //   String tableName = "wpp_asht_ope_his";
            List<String> columnList = new ArrayList<>();
            List<String> shopList = gpDao.findBySQL(sqlColumn.toString());
            if (shopList == null || shopList.size() <= 0) {
                logger.info("EXT 配置表中没有该厂别对应记录信息：shopId =  {}", shopId);
                return;
            }
            for (String target : shopList) {
                String ext = target.substring(0, 3);
                String s = target.substring(3, 4).toLowerCase();
                String number = target.substring(4);
                String columnName = ext + "_" + s + "_" + number;
                columnList.add(columnName);
            }
            if (columnList == null || columnList.size() <= 0) {
                logger.info("updateWppShtOpeInfoExtData 的 columnList中没有数据：即没有需要补数据的栏位；");
                return;
            }
            sqlbuff.append(" update wpp_sht_ope_info_ext e set ");
            columnList.forEach(e -> {

                sqlbuff.append(e + "=a." + e + ",");

            });
            sqlbuff.deleteCharAt(sqlbuff.length() - 1);


            sqlbuff.append("  from (select i.*,h.* from (select panel_id as panel,tft_glass_id,cf_glass_id from wpp_sht_info )i  ");
            sqlbuff.append(" left join (select * from wpp_sht_ope_info_ext where panel_id in  ");
            sqlbuff.append("  (select glass_id from  " + tableName + " where glass_id in(select panel_id from stage.wpp_sht_ope_info_ext_distinct))  )h  on  ");
            if ("ARRAY".equals(shopId)) {
                sqlbuff.append(" i.tft_glass_id =h.panel_id where panel_id is not null and panel_id<>panel )a where  e.panel_id =a.panel;   ");
            } else if ("CF".equals(shopId)) {
                sqlbuff.append(" i.cf_glass_id =h.panel_id where panel_id is not null  and panel_id<>panel )a where  e.panel_id =a.panel;  ");
            } else if ("OC".equals(shopId)) {
                sqlbuff.append("  i.panel =h.panel_id where h.panel_id is not null and h.panel_id  not in(i.tft_glass_id,i.cf_glass_id)   ");
                sqlbuff.append("  )a where  e.panel_id =a.cf_glass_id or e.panel_id =a.tft_glass_id  ;  ");
            } else {
                return;
            }


            SQLQuery queryLoadStep = session.createSQLQuery(sqlbuff.toString());
            int count = queryLoadStep.executeUpdate();
            logger.info(" 用{}数据补值wpp_sht_ope_info_ext  count :{}", shopId, count);
        } catch (Exception e) {
            logger.error("************  updateWppShtOpeInfoExtData SQL:  **********    " + sqlbuff.toString());
            logger.error(" 用{}数据补值wpp_sht_ope_info_ext  error ,对应补全栏位的 sql={}", shopId, sqlColumn.toString());
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new ETLException("ERROR updateWppShtOpeInfoExtData :" + e.getMessage());
        }
    }

}




